﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using FTools.Dominio;
using System.Data.SqlClient;
using System.Data;
using System.Collections;

namespace FTools.Persistencia
{
    class PerPersonaProveedor
    {
        static string msg = "";

        //OBTENER PERSONA Proveedor
        public PersonaProveedor obtenerPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            PersonaProveedor objPersonaProveedor = new PersonaProveedor();
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_PROVEEDOR where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];
                    PerRubro objPerRubro = new PerRubro();
                    Rubro objRubro = objPerRubro.obtenerRubro(xConexion, xTransaccion, (long)fila.ItemArray[2]);

                    objPersonaProveedor.IdPersona = (long)fila.ItemArray[0];
                    objPersonaProveedor.NombreProv = (String)fila.ItemArray[1]; objPersonaProveedor.NombreProv = objPersonaProveedor.NombreProv.Trim();
                    objPersonaProveedor.ObjRubro = objRubro;
                }
                return objPersonaProveedor;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //EXISTE PERSONA Proveedor
        public Boolean existePersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, long xIdPersona)
        {
            DataTable dataTable = new DataTable();
            try
            {
                SqlCommand command = new SqlCommand("select count(*) as EXISTE from PERSONA_PROVEEDOR where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xIdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                SqlDataAdapter dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                if (dataTable.Rows.Count >= 0)
                {
                    DataRow fila = dataTable.Rows[0];
                    if ((int)fila.ItemArray[0] > 0)
                    {
                        return true;
                    }
                }
                return false;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //OBTENER COLECCION PERSONA Proveedor
        public ArrayList obtenerColeccionPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersonaProveedor = new ArrayList();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_PROVEEDOR");

                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    PersonaProveedor objPersonaProveedor = new PersonaProveedor();
                    DataRow fila = dataTable.Rows[i];
                    PerRubro objPerRubro = new PerRubro();
                    Rubro objRubro = objPerRubro.obtenerRubro(xConexion, xTransaccion, (long)fila.ItemArray[2]);

                    objPersonaProveedor.IdPersona = (long)fila.ItemArray[0];
                    objPersonaProveedor.NombreProv = (String)fila.ItemArray[1]; objPersonaProveedor.NombreProv = objPersonaProveedor.NombreProv.Trim();
                    objPersonaProveedor.ObjRubro = objRubro;
                    colPersonaProveedor.Add(objPersonaProveedor);
                }
                return colPersonaProveedor;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }
        
        //OBTENER BUSQUEDA PERSONA Proveedor
        public ArrayList obtenerBusquedaPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, String xNombreProv, long xIdRubro)
        {
            SqlDataAdapter dataAdapter;
            DataTable dataTable = new DataTable();
            ArrayList colPersonaProveedor = new ArrayList();
            try
            {
                SqlCommand command = new SqlCommand("select * from PERSONA_PROVEEDOR where " +
                                                    "(NOMBRE like '%' + @NombreProv + '%' or @NombreProv='') and " +
                                                    "(ID_RUBRO=@IdRubro or @IdRubro=0)");

                command.Parameters.AddWithValue("@NombreProv", xNombreProv.Trim());
                command.Parameters.AddWithValue("@IdRubro", xIdRubro);

                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                dataAdapter = new SqlDataAdapter(command);
                dataAdapter.Fill(dataTable);
                for (int i = 0; i < dataTable.Rows.Count; i++)
                {
                    PersonaProveedor objPersonaProveedor = new PersonaProveedor();
                    DataRow fila = dataTable.Rows[i];
                    PerRubro objPerRubro = new PerRubro();
                    Rubro objRubro = objPerRubro.obtenerRubro(xConexion, xTransaccion, (long)fila.ItemArray[2]);

                    objPersonaProveedor.IdPersona = (long)fila.ItemArray[0];
                    objPersonaProveedor.NombreProv = (String)fila.ItemArray[1]; objPersonaProveedor.NombreProv = objPersonaProveedor.NombreProv.Trim();
                    objPersonaProveedor.ObjRubro = objRubro;

                    colPersonaProveedor.Add(objPersonaProveedor);
                }
                return colPersonaProveedor;
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //ALTA PERSONA Proveedor
        public int altaPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaProveedor xObjPersonaProveedor)
        {
            try
            {
                SqlCommand command = new SqlCommand("insert into PERSONA_PROVEEDOR values (@IdPersona, @NombreProv, @IdRubro)");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaProveedor.IdPersona);
                command.Parameters.AddWithValue("@NombreProv", xObjPersonaProveedor.NombreProv.Trim());
                command.Parameters.AddWithValue("@IdRubro", xObjPersonaProveedor.ObjRubro.IdRubro);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //BAJA PERSONA Proveedor
        public int bajaPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaProveedor xObjPersonaProveedor)
        {
            try
            {
                SqlCommand command = new SqlCommand("delete from PERSONA_PROVEEDOR where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaProveedor.IdPersona);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }

        //MODIFICA PERSONA Proveedor
        public int modificaPersonaProveedor(SqlConnection xConexion, SqlTransaction xTransaccion, PersonaProveedor xObjPersonaProveedor)
        {
            try
            {
                SqlCommand command = new SqlCommand("update PERSONA_PROVEEDOR set NOMBRE=@NombreProv, ID_RUBRO=@IdRubro " +
                                                    "where ID_PERSONA=@IdPersona");

                command.Parameters.AddWithValue("@IdPersona", xObjPersonaProveedor.IdPersona);
                command.Parameters.AddWithValue("@NombreProv", xObjPersonaProveedor.NombreProv.Trim());
                command.Parameters.AddWithValue("@IdRubro", xObjPersonaProveedor.ObjRubro.IdRubro);
                command.Connection = xConexion;
                command.Transaction = xTransaccion;
                return command.ExecuteNonQuery();
            }
            catch (SqlException ex)
            {
                for (int i = 0; i < ex.Errors.Count; i++)
                {
                    msg += "Error #" + i + " Message: " + ex.Errors[i].Message + "\n";
                }
                System.Console.WriteLine(msg);
                throw new Exception("#Persistencia# " + ex.Message);
            }
        }
    }
}
